import xlrd
import xlwt

path_ori = r'I:\Work\Pycharm\Projects\virtualenv\day01\app01\web.xls'
path_dst = r'I:\Work\Pycharm\Projects\virtualenv\day01\app01\res.xls'
sheets = ['dlgdownload.xml', 'dlgvideo.xml', 'nvroperlog.xml', 'camera.xml', 'camera.xml',
          'channel.xml', 'common.xml', 'ctrllib.xml', 'disk.xml', 'event.xml',
          'intellanalysis.xml', 'intellsearch.xml', 'locallocalconfig.xml', 'login.xml',
          'main.xml', 'network.xml', 'network.xml', 'systemconfig.xml', 'systemmaintain.xml',
          'vehicle.xml', 'video.xml', 'viewer.xml',
          ]


def write_excel(data, sheetName, path):
    '''
    写入一些比对结果
    :param data:            [[xxx,xxx,xxx],[xxx,xxx,xxx]...]
    :param sheetName:       写入到哪张表中
    :param path:            写入的文件路径
    :return:                None
    '''
    wb_wt = xlwt.Workbook(encoding='utf-8')

    sheet = wb_wt.add_sheet(sheetName)

    for row_num, row_data in enumerate(data):
        for col_num, value in enumerate(row_data):
            sheet.write(row_num, col_num, value)

    wb_wt.save(path)


def write_data(wb, sheetName, data):
    sheet = wb.add_sheet(sheetName)
    for row_num, row_data in enumerate(data):
        for col_num, value in enumerate(row_data):
            sheet.write(row_num, col_num, value)


def get_translate_gui(path):
    '''
    获取gui的翻译
    :param path:
    :return:    web&gui 的翻译列表 [[xxx,xxx,xxx],[xxx,xxx,xxx]...]
    '''
    gui_translate = []

    # 取GUI翻译
    wb = xlrd.open_workbook(path)
    sheet = wb.sheet_by_index(0)
    for row in range(sheet.nrows):
        gui_translate.append(sheet.row_values(row))

    return gui_translate


def get_translate_web(path):
    '''
    获取web的翻译
    :param path:
    :return:    web的翻译列表 [[xxx,xxx,xxx,xxx.xml],[xxx,xxx,xxx,xxx.xml]...]
    '''
    web_translate = []
    # 取GUI翻译
    wb = xlrd.open_workbook(path)

    # 取web翻译
    for sheet_name in sheets:
        name_sheet = wb.sheet_by_name(sheet_name)
        for row in range(name_sheet.nrows):
            row_list = name_sheet.row_values(row)
            row_list.append(sheet_name)
            web_translate.append(row_list)

    return web_translate


def gen_match_result():
    '''
    获取匹配结果
    :return: matched_translate(需写入表格 gui + web的比对结果)  not_matched_translate(写到表格，只有gui)
    '''
    # 写入匹配的数据
    gui_data = get_translate_gui(path_ori)
    web_data = get_translate_web(path_ori)
    web_data_slice = [i[1:3] for i in web_data]

    matched_translate = []
    not_matched_translate = []
    # 获取中文+英文 完全相同的项
    for item_gui in gui_data:
        for item_web in web_data:
            if item_gui[1:] == item_web[1:3]:
                matched_translate.append(item_gui + [''] + item_web)

    for item_gui in gui_data:
        if item_gui[1:] not in web_data_slice:
            not_matched_translate.append(item_gui)

    return matched_translate, not_matched_translate


# 是英文不匹配吗
# 用没有匹配到的项取web里再找一次
def zh_match_result(not_matched_translate, web_translate):
    '''
    查询中文匹配但是英文不匹配的结果，需要写入表格
    :param not_matched_data:    [xxx,xxx,xxx]
    :param web_translate:       [xxx,xxx,xxx,xxx.xml]
    :return:        (中文匹配但是英文不匹配的结果, 中文都没有匹配到的gui翻译)
    zh_matched [xxx,xxx,xxx,' ',xxx,xxx,xxx,xxx.xml](包括重复的，比实际更多一些)   zh_not_matched [xxx,xxx,xxx]（实际的数量）
    '''
    zh_matched = []
    zh_not_matched = []
    web_translate_slice = [i[1] for i in web_translate]

    for item_gui in not_matched_translate:
        for item_web in web_translate:
            # gui的中文 == web的中文
            if item_gui[1] == item_web[1]:
                # print(item_gui, item_web)

                zh_matched.append(item_gui + [''] + item_web)

        if item_gui[1] not in web_translate_slice:
            zh_not_matched.append(item_gui)

    return zh_matched, zh_not_matched


# 是中文不匹配吗
# 再拿GUI没有匹配到中文的翻译 --> 用英文和web比对，看看是不是英文对了，中文不对
# 最后： 剩下的全是中英文没有一个匹配的情况
def en_match_result(zh_not_matched, web_translate):
    '''

    :param zh_not_matched:
    :param web_translate:
    :return: en_matched [xxx,xxx,xxx,' ',xxx,xxx,xxx,xxx.xml](包括重复的，比实际更多一些)   en_not_matched [xxx,xxx,xxx]（实际的数量）
    '''
    en_matched = []
    en_not_matched = []
    web_translate_slice = [i[2] for i in web_translate]
    for item_gui in zh_not_matched:
        for item_web in web_translate:
            if item_gui[2] == item_web[2]:
                en_matched.append(item_gui + [''] + item_web)
        if item_gui[2] not in web_translate_slice:
            en_not_matched.append(item_gui)
    return en_matched, en_not_matched


def check_and_write():
    # 是有递进关系的
    # wb, sheetName, data
    work_book = xlwt.Workbook(encoding='utf-8')

    # GUI翻译写到Excel
    gui_translate = get_translate_gui(path_ori)
    write_data(work_book, 'GUI翻译汇总', gui_translate)
    # WEB翻译写到Excel
    web_translate = get_translate_web(path_ori)
    write_data(work_book, 'WEB翻译汇总', web_translate)
    # 中英文翻译非/完全一致的写到Excel
    matched_translate, not_matched_translate = gen_match_result()
    write_data(work_book, '中英全匹配', matched_translate)
    write_data(work_book, '非中英全匹配', not_matched_translate)
    # 从非中英全匹配中 --> 中文匹配但是英文不匹配，和中文都匹配不上的
    zh_matched, zh_not_matched = zh_match_result(not_matched_translate, web_translate)
    write_data(work_book, '只有中文匹配', zh_matched)
    write_data(work_book, '中文不匹配', zh_not_matched)

    # 从中文都匹配不上中 --> 英文匹配但中文不匹配，英文都不匹配的
    en_matched, en_not_matched = en_match_result(zh_not_matched, web_translate)
    write_data(work_book, '英文匹配', en_matched)
    write_data(work_book, '中英都不匹配', en_not_matched)

    work_book.save(path_dst)


if __name__ == '__main__':
    check_and_write()
